#!/usr/bin/python

# import MySQL module
import MySQLdb

#
# verifico que este la columna
# si no esta => la creo y retorno true
# si ya estaba => retorno false
# 
def CreateColumn( table, column, spec ):
    print column + '@' + table

    # creo un cursor para preguntar si existe la columna
    cursor = db.cursor()
    cursor.execute('SHOW COLUMNS FROM ' + table + ' LIKE %s', (column) )

    if cursor.rowcount == 0:
        # creo la columna
        cursor.execute('ALTER TABLE ' + table + ' ADD ' + column + ' ' + spec)
        print "creada..."    
        return 1

    # si no cree la columna => retorno 0 (false)
    print '..ya existia.'
    return 0


#
# IpLog.Id 
#
def Columna_IpLog_Id():
    if CreateColumn('IpLog', 'id', 'INT NULL AUTO_INCREMENT PRIMARY KEY'): 
        print "Listo..."
    return


#
# minutos desde que salio del area clasificada 
# hasta el momento de esta transaccion
# 
def Columna_minsSinceOutClassified():
    if CreateColumn('IpLog', 'minsSinceOutClassified', 'INT NULL'): 
        # itero en toda la tabla, y para cada registro calculo los minutos
        cursorIp = db.cursor()
        cursorIp.execute( 'SELECT id, EmployeeID, AccessTime FROM IpLog I, Employee E WHERE E.IP=I.SrcIP' )
        numRows = int(cursorIp.rowcount)
        for i in range(0, numRows):
            # leo la fila de IpLog
            ipRow = cursorIp.fetchone()
            idIp = int( ipRow[0] )
            idEmp = int( ipRow[1] )
            accT = ipRow[2]

            # obtengo la cantidad de minutos
            cursorMins = db.cursor()
            query = "SELECT CAST( TIME_TO_SEC( TIMEDIFF( %s, DateTime ) )/60 AS UNSIGNED INTEGER) FROM ProxLog"
            query += " WHERE EmployeeID = %s "
            query += " AND DateTime < %s"
            query += " AND TO_DAYS( DateTime ) = TO_DAYS(%s)"
            query += " AND Location = 'prox-out-classified'"
            query += " ORDER BY DateTime DESC LIMIT 1"
            cursorMins.execute( query, ( accT, idEmp, accT, accT ) )
            mins=None

            result = cursorMins.fetchone()
            if result:
                mins = result[0]

            if mins:
                cursorUpd = db.cursor()
                cursorUpd.execute( "UPDATE IpLog SET minsSinceOutClassified = %s WHERE id = %s", ( mins, idIp) )
                db.commit()
                #print "idIp: ", idIp, "  idEmp: ", idEmp, "  accT: ", accT, " -> mins: ", mins, " -updCount: ", cursorUpd.rowcount

        print "Listo..."
    return




# connect
db = MySQLdb.connect(host="localhost", user="vast", passwd="vast", db="VAST")

Columna_IpLog_Id()

Columna_minsSinceOutClassified()



print "Fin proceso"








# create a cursor
#cursor = db.cursor()

# execute SQL statement
#cursor.execute("INSERT INTO animals (name, species) VALUES (%s, %s)",(name, species))
# 
# print "ID of inserted record is ", int(cursor.insert_id())
